/**
 * Copyright (c) 2018 人人开源 All rights reserved.
 * <p>
 * https://www.renren.io
 * <p>
 * 版权所有，侵权必究！
 */

package org.edu.modules.stu.base.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.ParseException;
import java.util.*;

import org.apache.poi.ss.usermodel.Cell;

/**
 * excel工具类
 *
 * @author Mark sunlightcs@gmail.com
 */
public class ExcelUtils {
    /**
     * excell 2003 表格后缀
     */
    public static final String EXCELL_2003_SUFFIX = "xls";
    /**
     * excell 2007 表格后缀
     */
    public static final String EXCELL_2007_SUFFIX = "xlsx";




    /**
     * Excel导出
     *
     * @param response  response
     * @param fileName  文件名
     * @param sheetName sheetName
     * @param list      数据List
     * @param pojoClass 对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<?> list,
                                   Class<?> pojoClass) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            //当前日期
            fileName = DateUtils.format(new Date());
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), pojoClass).sheet(sheetName).doWrite(list);
    }

    /**
     * Excel导出，先sourceList转换成List<targetClass>，再导出
     *
     * @param response    response
     * @param fileName    文件名
     * @param sheetName   sheetName
     * @param sourceList  原数据List
     * @param targetClass 目标对象Class
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, String sheetName, List<?> sourceList,
                                           Class<?> targetClass) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for (Object source : sourceList) {
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }

        exportExcel(response, fileName, sheetName, targetList, targetClass);
    }

    /**
     * <p>将Excel2003转成Json数据</p>
     *
     * @param inputStream 输入流
     * @return JSONArray 数组
     * @throws IOException            IO 异常
     * @throws InvalidFormatException 无效格式化异常
     * @author LanChaoHui
     * @date 2020/11/17 11:51
     * @since 0.0.1
     */
    public static JSONArray jsonExcel2003(InputStream inputStream) throws IOException, InvalidFormatException {
        Workbook workbook = WorkbookFactory.create(inputStream);
        // 创建公式计算器
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = workbook.getSheetAt(0);
        return analysisData(sheet, evaluator);
    }

    /**
     * <p>解析Excel2007转成JSON数据</p>
     *
     * @param inputStream 输入流
     * @return JSONArray 数组
     * @throws IOException IO 异常
     * @author LanChaoHui
     * @date 2020/11/17 11:54
     * @since 0.0.1
     */
    public static JSONArray jsonExcel2007(InputStream inputStream) throws IOException {
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 创建公式计算器
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = workbook.getSheetAt(0);
        return analysisData(sheet,evaluator);
    }

    /**
     * <p>判断是否是Excel2007</p>
     *
     * @param multipartFile 多文件
     * @return boolean true 是Excel 2007 false 不是Excel2007
     * @author LanChaoHui
     * @date 2020/11/17 11:55
     * @since 0.0.1
     */
    public static boolean isExcel2007(MultipartFile multipartFile) {
        String uploadName = multipartFile.getOriginalFilename();
        String suffix = uploadName.substring(uploadName.indexOf("."));
        System.out.println(".xlsx".equalsIgnoreCase(suffix));
        return ".xlsx".equalsIgnoreCase(suffix);
    }

    /**
     * <p>导出excel文件</p>
     *
     * @param response    响应
     * @param fileName    文件名
     * @param fileType    文件类型
     * @param inputStream 文件输入流
     * @author LanChaoHui
     * @date 2020/11/17 9:35
     * @since 0.0.1
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String fileType, InputStream inputStream) throws IOException, InvalidFormatException {
        if (StringUtils.isBlank(fileName)) {
            fileName = DateUtils.format(new Date());
        }
        Workbook workbook = null;
        if (fileType.equals(EXCELL_2003_SUFFIX)) {
            workbook = excel2003Handle(inputStream, response, fileName);
        }
        if (fileType.equals(EXCELL_2007_SUFFIX)) {
            workbook = excel2007Handle(inputStream, response, fileName);
        }
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
    }

    /**
     * <p>world 2007 响应头设置</p>
     *
     * @param inputStream 输入流
     * @param response    响应
     * @param fileName    文件名
     * @return Workbook
     * @author LanChaoHui
     * @date 2021/1/7 15:42
     * @since 0.0.1
     */
    private static Workbook excel2007Handle(InputStream inputStream, HttpServletResponse response, String fileName) throws IOException {
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1") + "." + EXCELL_2007_SUFFIX);
        response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        Workbook workbook = new XSSFWorkbook(inputStream);
        return workbook;
    }

    /**
     * <p>world 2003 响应头设置</p>
     *
     * @param inputStream 输入流
     * @param response    响应
     * @param fileName    文件名
     * @return Workbook
     * @author LanChaoHui
     * @date 2021/1/7 15:42
     * @since 0.0.1
     */
    private static Workbook excel2003Handle(InputStream inputStream, HttpServletResponse response, String fileName) throws IOException {
        response.setHeader("Content-Disposition", "attachment;filename=contacts" + new String(fileName.getBytes("gbk"), "iso8859-1") + "." + EXCELL_2003_SUFFIX);
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        return workbook;
    }

    /**
     * <p>解析数据</p>
     *
     * @param sheet excel 页数据
     * @return JSONArray json 数组数据
     * @author LanChaoHui
     * @date 2020/11/17 11:49
     * @since 0.0.1
     */
    private static JSONArray analysisData(Sheet sheet, FormulaEvaluator evaluator) {

        JSONArray jsonArray = new JSONArray();
        int rowEnd = sheet.getPhysicalNumberOfRows();
        String[] title = null;
        for (int i = 0; i < rowEnd; i++) {
            Row row = sheet.getRow(i);
            int cellstart = row.getFirstCellNum();
            int cellend = row.getLastCellNum();
            // 过滤出表头
            if (i == 2) {
                title = new String[cellend];
                for (int j = cellstart; j < cellend; j++) {
                    Cell cell = row.getCell(j);
                    cell.setCellType(CellType.STRING);
                    if (StringUtils.isNotBlank(cell.getStringCellValue())) {
                        title[j] = cell.getStringCellValue();
                    }
                }
                title = filterNull(title);
            } else if (i >= 3) {
                JSONObject jsonObject = new JSONObject();
                for (int j = 0; j < title.length; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }

                    //处理带公式的excel数据
                    if (cell.getCellType() == CellType.FORMULA.getCode()) {
                        CellValue cellValue = evaluator.evaluate(cell);

                        if (cellValue != null) {
                            // 5. 根据类型获取结果
                            switch (cellValue.getCellTypeEnum()) {
                                case STRING:
                                    cell.setCellType(CellType.STRING);
                                    String value = cell.getStringCellValue();
                                    if (StringUtils.isBlank(value) && jsonObject.isEmpty()) {
                                        continue;
                                    }
                                    break;
                                case NUMERIC:
                                    cell.setCellType(CellType.NUMERIC);
                                    Double doubleValue = cell.getNumericCellValue();
                                    if (doubleValue==null) {
                                        continue;
                                    }
                                    break;
                                case BOOLEAN:
                                    cell.setCellType(CellType.BOOLEAN);
                                    Boolean booleanValue = cell.getBooleanCellValue();
                                    if (booleanValue == null) {
                                        continue;
                                    }
                                    break;
                                case ERROR:
                                    cell.setCellType(CellType.ERROR);
                                    byte errorValue = cell.getErrorCellValue();
                                    if (errorValue == 0) {
                                        continue;
                                    }
                                    break;
                            }
                        }
                    } else {
                        cell.setCellType(CellType.STRING);
                        String value = cell.getStringCellValue();
                        if (StringUtils.isBlank(value) && jsonObject.isEmpty()) {
                            continue;
                        }
                    }

                    // 5. 根据类型写入结果
                    switch (cell.getCellTypeEnum()) {
                        case STRING:
                            jsonObject.put(title[j], cell.getStringCellValue());
                            break;
                        case NUMERIC:
                            cell.setCellType(CellType.NUMERIC);
                            jsonObject.put(title[j], cell.getNumericCellValue());
                            break;
                        case BOOLEAN:
                            cell.setCellType(CellType.BOOLEAN);
                            jsonObject.put(title[j], cell.getBooleanCellValue());
                            break;
                        case ERROR:
                            cell.setCellType(CellType.ERROR);
                            jsonObject.put(title[j], cell.getErrorCellValue());
                            break;
                    }

                }
                if (!jsonObject.isEmpty()) {
                    jsonArray.add(jsonObject);
                }
            }

        }
        return jsonArray;
    }




    /**
     * <p>为字符串宿主过滤空字符串，并返回</p>
     *
     * @param strList 字符串集合
     * @return 字符串数组
     * @author LanChaoHui
     * @date 2020/11/17 11:47
     * @since 0.0.1
     */
    private static String[] filterNull(String[] strList) {
        StringBuilder stringBuffer = new StringBuilder();
        for (int i = 0; i < strList.length; i++) {
            String str = strList[i];
            if (StringUtils.isNotBlank(str)) {
                stringBuffer.append(str);
                if (i != strList.length - 1) {
                    stringBuffer.append(";");
                }
            }
        }
        return stringBuffer.toString().split(";");
    }

    /**
     * <p>将EXCEL文件内的数据转换成JSONArray数组</p>
     *
     * @param multipartFile 多文件
     * @return JSONArray 数组
     * @throws IOException            IO 异常
     * @throws InvalidFormatException 无效格式化异常
     * @author LanChaoHui
     * @date 2020/11/17 11:56
     * @since 0.0.1
     */
    public static JSONArray excelToJson(MultipartFile multipartFile) throws IOException, InvalidFormatException {
        InputStream inputStream = multipartFile.getInputStream();
        return isExcel2007(multipartFile) ? jsonExcel2007(inputStream) : jsonExcel2003(inputStream);
    }
    /**Excel导入**/
    public static <T> List<T> importExcel(MultipartFile multipartFile,Class<T> clazz) throws Exception {
        JSONArray jsonArray = excelToJson(multipartFile);
       return mapJsonToEntity(jsonArray,clazz);
    }
    /**JsonArray转任意实体类，根据@Excel注解的name进行映射字段 */
//    public static <T> List<T> mapJsonToEntity(JSONArray jsonArray, Class<T> clazz) throws Exception {
//        List<T> result = new ArrayList<>();
//
//        // 1. 构建注解映射关系
//        Map<String, Field> fieldMap = new HashMap<>();
//        for (Field field : clazz.getDeclaredFields())  {
//            Excel excelAnno = field.getAnnotation(Excel.class);
//            if (excelAnno != null) {
//                fieldMap.put(excelAnno.name().trim(),  field);
//            }
//        }
//
//        // 2. 遍历JSON数据
//        for (Object item : jsonArray) {
//            JSONObject jsonObj = (JSONObject) item;
//            T entity = clazz.newInstance();
//
//            // 3. 动态注入字段值
//            for (Map.Entry<String, Object> entry : jsonObj.entrySet())  {
//                String excelHeader = entry.getKey().trim();
//                Field field = fieldMap.get(excelHeader);
//                if (field != null) {
//                    field.setAccessible(true);
//                    // 类型转换（示例处理String->Integer）
//                    Object value = entry.getValue();
//                    if (field.getType()  == Integer.class)  {
//                        value = Integer.parseInt(value.toString());
//                    }else if (field.getType()  == Double.class)  {
//                        value = Double.parseDouble(value.toString());
//                    }else if (field.getType()  == String.class)  {
//                        value = value.toString();
//                    }else if (field.getType()  == Boolean.class)  {
//                        value = Boolean.parseBoolean(value.toString());
//                    }else if (field.getType()  == Date.class)  {
//                        value = DateUtils.parseDate(value.toString());
//                    }else if (field.getType()  == Long.class)  {
//                        value = Long.parseLong(value.toString());
//                    }else if (field.getType()  == BigDecimal.class)  {
//                        value = BigDecimal.valueOf(Double.parseDouble(value.toString()));
//                    }
//
//                    field.set(entity,  value);
//                }
//            }
//            result.add(entity);
//        }
//        return result;
//    }
    /**
     * 将JSON数组转换为指定实体类的列表，根据@Excel注解的name进行字段映射
     *
     * @param jsonArray JSON数组
     * @param clazz     目标实体类的Class对象
     * @param <T>       目标实体类的类型
     * @return 实体类列表
     * @throws Exception 反射创建实例时可能抛出的异常
     */
    public static <T> List<T> mapJsonToEntity(JSONArray jsonArray, Class<T> clazz) throws Exception {
        List<T> result = new ArrayList<>();

        // 1. 构建注解映射关系
        Map<String, Field> fieldMap = buildFieldMap(clazz);

        // 2. 遍历JSON数据
        for (Object item : jsonArray) {
            JSONObject jsonObj = (JSONObject) item;
            T entity = clazz.newInstance();

            // 3. 动态注入字段值
            injectFieldValues(jsonObj, entity, fieldMap);
            result.add(entity);
        }
        return result;
    }

    /**
     * 构建注解映射关系
     *
     * @param clazz 目标实体类的Class对象
     * @return 注解名称到字段的映射关系
     */
    private static Map<String, Field> buildFieldMap(Class<?> clazz) {
        Map<String, Field> fieldMap = new HashMap<>();
        for (Field field : clazz.getDeclaredFields())  {
            Excel excelAnno = field.getAnnotation(Excel.class);
            if (excelAnno != null) {
                fieldMap.put(excelAnno.name().trim(),  field);
            }
        }
        return fieldMap;
    }

    /**
     * 动态注入字段值
     *
     * @param jsonObj  JSON对象
     * @param entity   目标实体类的实例
     * @param fieldMap 注解名称到字段的映射关系
     * @throws IllegalAccessException 反射设置字段值时可能抛出的异常
     */
    private static <T> void injectFieldValues(JSONObject jsonObj, T entity, Map<String, Field> fieldMap) throws IllegalAccessException {
        for (Map.Entry<String, Object> entry : jsonObj.entrySet())  {
            String excelHeader = entry.getKey().trim();
            Field field = fieldMap.get(excelHeader);
            if (field != null) {
                field.setAccessible(true);
                Object value = convertValue(entry.getValue(),  field.getType());
                if (value != null) {
                    field.set(entity,  value);
                }
            }
        }
    }

    /**
     * 类型转换方法
     *
     * @param value  原始值
     * @param target 目标类型
     * @return 转换后的值
     */
    private static Object convertValue(Object value, Class<?> target) {
        if (value == null) {
            return null;
        }
        String valueStr = value.toString().trim();
        try {
            if (target == Integer.class)  {
                return Integer.parseInt(valueStr);
            } else if (target == Double.class)  {
                return Double.parseDouble(valueStr);
            } else if (target == String.class)  {
                return valueStr;
            } else if (target == Boolean.class)  {
                return Boolean.parseBoolean(valueStr);
            } else if (target == Date.class)  {
                return DateUtils.parseDate(valueStr);
            } else if (target == Long.class)  {
                return Long.parseLong(valueStr);
            } else if (target == BigDecimal.class)  {
                return BigDecimal.valueOf(Double.parseDouble(valueStr));
            }
        } catch (NumberFormatException | NullPointerException | ParseException e) {
            System.err.println(" 类型转换失败: " + e.getMessage());
        }
        return null;
    }
    /**
     * <p>写入excel</p>
     *
     * @param title             sheet 名
     * @param data              需要填充到excel的数据
     * @param heads             表头
     * @param excelWriter       Excel的写入对象
     * @param cellWriteHandlers 表格处理
     * @author LanChaoHui
     * @date 2021/2/4 11:13
     * @since 0.0.1
     */
    public static void writeExcel(String title, List<?> data, List<List<String>> heads, ExcelWriter excelWriter, CellWriteHandler... cellWriteHandlers) {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.writerSheet(title);
        excelWriterSheetBuilder.head(heads);
        excelWriterSheetBuilder.registerWriteHandler(horizontalCellStyleStrategy);
        excelWriterSheetBuilder.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20));
        for (CellWriteHandler cellWriteHandler : cellWriteHandlers) {
            excelWriterSheetBuilder.registerWriteHandler(cellWriteHandler);
        }
        WriteSheet sheet = excelWriterSheetBuilder.build();
        excelWriter.write(data, sheet);
    }

}
